package org.example.managementsystem.repository;

import org.example.managementsystem.model.ProviderComplaint;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 供应商投诉数据访问对象
 * @author 王振祎
 * @date 2025-05-18
 */
@Repository
public class ProviderComplaintDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ProviderComplaintDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 插入供应商投诉
     */
    public int insertComplaint(ProviderComplaint complaint) {
        String sql = "INSERT INTO provider_complaint (defective_material_number, is_approved, provider_name, description, complainant_job_number, status) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        jdbcTemplate.update(sql,
                complaint.getDefectiveMaterial(),
                complaint.isApproved(),
                complaint.getProviderName(),
                complaint.getDescription(),
                complaint.getComplainantJobNumber(),
                complaint.isStatus());
        // 获取刚刚插入的 id
        String getLastIdSql = "SELECT LAST_INSERT_ID()";
        int lastInsertId = jdbcTemplate.queryForObject(getLastIdSql, Integer.class);
        return lastInsertId;
    }

    /**
     * 更新供应商投诉状态
     */
    public int updateComplaintStatus(int complaintId, boolean isApproved, String description) {
        String sql = "UPDATE provider_complaint SET is_approved = ?, description = ? WHERE complaint_id = ?";
        return jdbcTemplate.update(sql, isApproved, description, complaintId);
    }

    public int updateComplaintStatus(int complaintId, boolean isApproved, boolean status) {
        String sql = "UPDATE provider_complaint SET is_approved = ?, status = ? WHERE complaint_id = ?";
        return jdbcTemplate.update(sql, isApproved, status, complaintId);
    }

    public int updateComplaintStatus(int complaintId, boolean isApproved) {
        String sql = "UPDATE provider_complaint SET is_approved = ? WHERE complaint_id = ?";
        return jdbcTemplate.update(sql, isApproved, complaintId);
    }

    public int updateComplaint(ProviderComplaint complaint){
        String sql = "UPDATE provider_complaint SET defective_material_number = ?, is_approved = ?, provider_name = ?, description = ? ,status = ? WHERE complaint_id = ?";
        return jdbcTemplate.update(sql, complaint.getDefectiveMaterial(), complaint.isApproved(), complaint.getProviderName(), complaint.getDescription(), complaint.isStatus(), complaint.getComplaintId());
    }


    /**
     * 查询所有投诉
     */
    public List<ProviderComplaint> getAllComplaints() {
        String sql = "SELECT * FROM provider_complaint";
        return jdbcTemplate.query(sql, new ProviderComplaintRowMapper());
    }

    /**
     * 查询所有未审核的投诉
     */
    public List<ProviderComplaint> getAllUnreviewedComplaints() {
        String sql = "SELECT * FROM provider_complaint WHERE status = 0";
        return jdbcTemplate.query(sql, new ProviderComplaintRowMapper());
    }

    /**
     * 根据ID查询投诉
     */
    public ProviderComplaint getComplaintById(int complaintId) {
        String sql = "SELECT * FROM provider_complaint WHERE complaint_id = ?";
        List<ProviderComplaint> complaints = jdbcTemplate.query(sql, new ProviderComplaintRowMapper(), complaintId);
        return complaints.isEmpty() ? null : complaints.get(0);
    }

    /**
     * 投诉行映射器
     */
    private static class ProviderComplaintRowMapper implements RowMapper<ProviderComplaint> {
        @Override
        public ProviderComplaint mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProviderComplaint complaint = new ProviderComplaint();
            complaint.setComplaintId(rs.getInt("complaint_id"));
            complaint.setDefectiveMaterial(rs.getString("defective_material_number"));
            complaint.setProviderName(rs.getString("provider_name"));
            complaint.setDescription(rs.getString("description"));
            complaint.setComplainantJobNumber(rs.getString("complainant_job_number"));
            complaint.setApproved(rs.getBoolean("is_approved"));
            complaint.setStatus(rs.getBoolean("status"));
            return complaint;
        }
    }

    /**
     * 根据ID删除投诉
     */
    public int deleteComplaint(int complaintId) {
        String sql = "DELETE FROM provider_complaint WHERE complaint_id = ?";
        return jdbcTemplate.update(sql, complaintId);
    }
}